package com.xzy.demo1;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * 从Excel读取数据
 *
 * @author xzy
 * @date 2021/12/17 9:23
 */
public class Main {
    public static void main(String[] args) throws IOException {
        method3();
    }

    /**
     * 在Excel格式不明确的情况下读取数据
     */
    private static void method1() throws IOException {
        // 1.获取文件输入流
        InputStream inputStream = new FileInputStream("src/main/resources/六年1班期中考试成绩.xlsx");

        // 2.获取Excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        // 3.获取Excel工作表
        XSSFSheet sheet = workbook.getSheetAt(0);

        // 4.读取表格数据
        for (Row row : sheet) {
            StringBuilder context = new StringBuilder().append(row.getRowNum());
            // 根据单元格的
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                    case STRING:
                        context.append(" ").append(cell.getStringCellValue());
                        break;
                    case BOOLEAN:
                        context.append(" ").append(cell.getBooleanCellValue());
                        break;
                    case NUMERIC:
                        context.append(" ").append(cell.getNumericCellValue());
                        break;
                    default:

                }
            }
            System.out.println(context);
        }

        // 5.关闭流
        workbook.close();
    }

    /**
     * 在Excel格式不明确的情况下读取数据
     */
    private static void method2() throws IOException {
        // 1.获取文件输入流
        InputStream inputStream = new FileInputStream("src/main/resources/六年1班期中考试成绩.xlsx");

        // 2.获取Excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        // 3.获取Excel工作表
        XSSFSheet sheet = workbook.getSheetAt(0);

        // 4.读取表格数据
        for (Row row : sheet) {
            StringBuilder context = new StringBuilder().append(row.getRowNum());
            for (Cell cell : row) {
                // 设置单元格为STRING类型
                cell.setCellType(CellType.STRING);
                context.append(" ").append(cell.getStringCellValue());
            }
            System.out.println(context);
        }

        // 5.关闭流
        workbook.close();
    }

    /**
     * 在Excel格式明确的情况下读取数据
     */
    private static void method3() throws IOException {
        // 1.获取文件输入流
        InputStream inputStream = new FileInputStream("src/main/resources/六年1班期中考试成绩.xlsx");

        // 2.获取Excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        // 3.获取Excel工作表
        XSSFSheet sheet = workbook.getSheetAt(0);

        // 4.读取表格数据
        for (Row row : sheet) {
            StringBuilder context = new StringBuilder().append(row.getRowNum());
            if (row.getRowNum() == 0) {  // 0-based
                // 标题行
                context.append(" ").append(row.getCell(0).getStringCellValue()); // 0-based
                context.append(" ").append(row.getCell(1).getStringCellValue());
                context.append(" ").append(row.getCell(2).getStringCellValue());
                context.append(" ").append(row.getCell(3).getStringCellValue());
                context.append(" ").append(row.getCell(4).getStringCellValue());
                context.append(" ").append(row.getCell(5).getStringCellValue());
                context.append(" ").append(row.getCell(6).getStringCellValue());
                context.append(" ").append(row.getCell(7).getStringCellValue());
                context.append(" ").append(row.getCell(8).getStringCellValue());
            } else {
                // 内容行
                context.append(" ").append(row.getCell(0).getStringCellValue());
                context.append(" ").append(row.getCell(1).getStringCellValue());
                context.append(" ").append(row.getCell(2).getStringCellValue());
                context.append(" ").append(row.getCell(3).getNumericCellValue());
                context.append(" ").append(row.getCell(4).getNumericCellValue());
                context.append(" ").append(row.getCell(5).getNumericCellValue());
                context.append(" ").append(row.getCell(6).getNumericCellValue());
                context.append(" ").append(row.getCell(7).getBooleanCellValue());
                context.append(" ").append(row.getCell(8).getDateCellValue());
            }
            System.out.println(context);
        }

        // 5.关闭流
        workbook.close();
    }

    /**
     * 读取Excel时常用的对象
     */
    private static void method4(InputStream inputStream) throws IOException {
        // 1.Excel
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        // 2.Sheet
        XSSFSheet sheet = workbook.getSheet("sheetName");
        XSSFSheet sheet1 = workbook.getSheetAt(0); // 0-based

        // 3.行
        XSSFRow row = sheet.getRow(0); // 0-based

        // 4.单元格
        XSSFCell cell = row.getCell(0); // 0-based

        // 5.单元格格式
        XSSFCellStyle cellStyle = cell.getCellStyle();

        // 6.单元格数据类型
        CellType cellType = cell.getCellType();
    }
}
